
package system.base;

import java.sql.ResultSet;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.hitrust.gs.bean.GSProductTypeBean;
import com.hitrust.gs.dao.GSProductTypeDao;
import com.hitrust.gs.util.DefineDic;

import framework.core.ConstDefine;
import framework.core.DBConnection;
import framework.core.DBEngine;
import framework.core.DBResult;
import framework.core.DateUtil;
import framework.core.IHandler;
import framework.core.ILoginContext;
import framework.core.Logger;
import framework.core.ObjectList;
import framework.core.SearchEvent;
import framework.core.SrvContext;
import framework.core.StringUtil;
import framework.core.UniversalRow;
/*
	Author    :Wing
	CreateTime:2008-02-20 17:44:59
*/
public class GetStatisticSrv extends IHandler
{

	public GetStatisticSrv() 
	{
		super();
	}
/*	private void deleteOutHead(DBConnection con,SrvContext context) throws Exception
	{
		String[] ids = context.getParameterValues("ids");
		OutHead obj = null;
		for(int i=0;i<ids.length;i++)
		{
			obj = OutHead.fetch(con,ids[i]);
			if(obj!=null) obj.delete(con);
		}
	}
	private void saveOutHead(DBConnection con,SrvContext context,ILoginContext loginContext) throws Exception
	{
		String id = context.getString("Id",null);
		OutHead obj;
		if((id==null)||(id.equals("")))   //add
		{
			obj = OutHead.fetch();
    	}
    	else                     //update
    	{
    		obj = OutHead.fetch(con,id);
    	}
		OutHeadPageData data = (OutHeadPageData)PageData.fromForm(context,OutHeadPageData.class);
		context.setValue("OutHeadPageData",data);
		obj.fill(data);
		obj.save(con);
	}*/
	private void listGetStatistic(DBConnection con,SrvContext context,ILoginContext loginContext,String path) throws Exception
	{
	    boolean isFirst = context.getBoolean("isFirst");
	    GetStatisticSearchEvent event = (GetStatisticSearchEvent)SearchEvent.fromForm(context,GetStatisticSearchEvent.class);
        String []warehouseCodes = null;
        String[] warehouseCodesSplit;
        String warehouseCodesService ="";
        String[] warehouseTreeCodes ;
        String isFirstVisit = "false";
        if (isFirst)
        {
        	isFirstVisit = "true";
        	warehouseTreeCodes = new String[]{loginContext.getLoginWarehouseCode()}; 
            warehouseCodes = new String[]{loginContext.getLoginWarehouseCode()}; 
        }
        else
        {
            warehouseCodes = context.getParameterValues("WarehouseCodes");
            if (warehouseCodes == null||warehouseCodes.length==0||warehouseCodes[0].equals(""))
            {
                warehouseCodes = new String[]{"--1"};
                warehouseTreeCodes = new String[]{"--1"};
            }else{
            	warehouseCodesSplit = warehouseCodes[0].split(",");
            	for (int j = 0; j < warehouseCodesSplit.length; j++) {
	        		String warehouseObjectListService = Warehouse.fetchWarehousesForString(con, warehouseCodesSplit[j]);
    				String[] service = warehouseObjectListService.split(",");
    				for (int i = 0; i < service.length; i++) {
    					warehouseCodesService += service[i]+",";
    				}
				}
            	warehouseCodes = warehouseCodesService.split(",");
            	warehouseTreeCodes =  context.getParameterValues("WarehouseCodes");
            }
            isFirstVisit = "false";
        }
        event.setWarehouseTreeCodes(warehouseTreeCodes);
        event.setWarehouseCodes(warehouseCodes);
        event.setWarehouseCodeUser(ConstDefine.getWildCard(loginContext.getLoginWarehouseCode()));
		context.setSessionValue("GetStatisticSearchEvent",event);
		context.setNextUrl(path+"GetStatisticList.jsp"+"?isFirstVisit="+isFirstVisit);
	}
/*	private void backOutHead(DBConnection con,SrvContext context,String path) throws Exception
	{
		context.setNextUrl(path+"OutHeadList.jsp");
	}
	*/
	
	//add by Wing start
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,String aValue){
        HSSFCell tCell = aRow.createCell(column);
        tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
        tCell.setCellValue(aValue);
        return tCell;
    }
	
	private void exportGetStatistic(DBConnection con,SrvContext context,String path) throws Exception
    {
	    HSSFWorkbook tWb = new HSSFWorkbook();
        HSSFSheet tSheet = tWb.createSheet();
        tWb.setSheetName(0,"领料情况统计",HSSFCell.ENCODING_UTF_16);
        HSSFRow tRow = tSheet.createRow((short)0);
        HSSFCell tCell0 = createCell(tWb,tRow,(short)0,"仓库");
        HSSFCell tCell1 = createCell(tWb,tRow,(short)1,"领料单位");
        HSSFCell tCell2 = createCell(tWb,tRow,(short)2,"总金额");
        
        GetStatisticSearchEvent event = (GetStatisticSearchEvent)context.getSessionValue("GetStatisticSearchEvent");
        ObjectList list = GetStatistic.fetch().detailqueryGroup(con,event);
        String result = (String)list.get(list.size()-1);
        for(int i=0;i<list.size()-1;i++)
        {
            UniversalRow lRow = (UniversalRow)list.get(i);
            HSSFRow row = tSheet.createRow((short)i+1);
            HSSFCell cell0 = createCell(tWb,row,(short)0,lRow.getString(1));
            HSSFCell cell1 = createCell(tWb,row,(short)1,lRow.getString(2));
            //郑斌 2008-03-21 10:01 begin 
            String numberStr = lRow.getString(3);
            numberStr = StringUtil.showPrice(numberStr);
            HSSFCell cell2 = createCell(tWb,row,(short)2,numberStr);
            //郑斌 2008-03-21 10:01 end
        }
        
        //总合计
        HSSFRow row = tSheet.createRow((short)list.size());
        HSSFCell cell0 = createCell(tWb,row,(short)0,"");
        HSSFCell cell1 = createCell(tWb,row,(short)1,"当前条件总金额:");
        HSSFCell cell2 = createCell(tWb,row,(short)2,StringUtil.showPrice(result));
        
        DateUtil date = new DateUtil();
        String year = date.getYear() + "";
        String month = date.getMonth() + "";
        if (date.getMonth() < 10) month = "0" + date.getMonth();
        String day = date.getDay() + "";
        if (date.getDay() < 10) day = "0" + date.getDay();
        String hour = date.getHour() + "";
        if (date.getHour() < 10) hour = "0" + date.getHour();
        String minute = date.getMinute() + "";
        if (date.getMinute() < 10) minute = "0" + date.getMinute();
        String second = date.getSecond() + "";
        if (date.getSecond() < 10) second = "0" + date.getSecond();
        
        String fileName = "OUT_BILL_" + year + "_" + month + "_" + day + "_" + hour + "_" + minute + "_" + second + ".xls";
        context.m_res.setContentType("application/ms-excel");
        context.m_res.addHeader("Content-disposition", "filename="+fileName);
        ServletOutputStream out = context.getServletOutputStream();
        tWb.write(out);
    }
	
	
	 private void exportDetailStatistic(DBConnection con,SrvContext context,String path,String customerCode) throws Exception
	    {
	        HSSFWorkbook tWb = new HSSFWorkbook();
	        HSSFSheet tSheet = tWb.createSheet();
	        tWb.setSheetName(0,"出库统计",HSSFCell.ENCODING_UTF_16);
	        HSSFRow tRow = tSheet.createRow((short)0);
	        HSSFCell tCell0  = createCell(tWb,tRow,(short)0, "仓库");
	        HSSFCell tCell1  = createCell(tWb,tRow,(short)1, "货位");
	        HSSFCell tCell2  = createCell(tWb,tRow,(short)2, "出库单编码");
	        HSSFCell tCell3  = createCell(tWb,tRow,(short)3, "出库日期");
	        HSSFCell tCell4  = createCell(tWb,tRow,(short)4, "客户");
	        HSSFCell tCell5  = createCell(tWb,tRow,(short)5, "出库类型");
	        HSSFCell tCell6  = createCell(tWb,tRow,(short)6, "领料单号");
	        HSSFCell tCell7  = createCell(tWb,tRow,(short)7, "供应商");
	        HSSFCell tCell8  = createCell(tWb,tRow,(short)8, "报修单号");
	        HSSFCell tCell9  = createCell(tWb,tRow,(short)9, "车号");
	        HSSFCell tCell10  = createCell(tWb,tRow,(short)10, "产品编码");
	        HSSFCell tCell11 = createCell(tWb,tRow,(short)11,"产品名称");
	        HSSFCell tCell12 = createCell(tWb,tRow,(short)12,"单位");
	        HSSFCell tCell13 = createCell(tWb,tRow,(short)13,"规格");
	        HSSFCell tCell14 = createCell(tWb,tRow,(short)14,"型号");
	        HSSFCell tCell15 = createCell(tWb,tRow,(short)15,"品牌");
	        HSSFCell tCell16 = createCell(tWb,tRow,(short)16,"数量");
	        HSSFCell tCell17 = createCell(tWb,tRow,(short)17,"单价");
	        HSSFCell tCell18 = createCell(tWb,tRow,(short)18,"金额");
	        HSSFCell tCell19 = createCell(tWb,tRow,(short)19,"领料单位");
	        HSSFCell tCell20 = createCell(tWb,tRow,(short)20,"领料人");
	        HSSFCell tCell21 = createCell(tWb,tRow,(short)21,"结算模式");
	        HSSFCell tCell22 = createCell(tWb,tRow,(short)22,"保管员");
	        HSSFCell tCell23 = createCell(tWb,tRow,(short)23,"创建人");
	        HSSFCell tCell24 = createCell(tWb,tRow,(short)24,"创建时间");
	        HSSFCell tCell25 = createCell(tWb,tRow,(short)25,"仓库分类");
	        HSSFCell tCell26 = createCell(tWb,tRow,(short)26,"备注");
	        HSSFCell tCell27 = createCell(tWb,tRow,(short)27,"区域");
	        
	        GetStatisticSearchEvent event = (GetStatisticSearchEvent)context.getSessionValue("GetStatisticSearchEvent");
	        event.setGetCustomerCode(customerCode);
	        event.setPageNum(1);
	        event.setPageSize(ConstDefine.INT_MAX);
	        ObjectList list = OutSub.fetch().search(con,event);
	        
	        String status = event.getStatus();
	        String statustp = event.getStatus();
	        if(statustp == null){
	            statustp = OutSub.STATUS_AVALIBLE;
	        }
	        else if("".equals(statustp)){
	            statustp = OutSub.STATUS_AVALIBLE;
	        }
	        event.setStatus(statustp);
	        
	      //-----------------------
	        String sql = "select sum(Product_Money) from T_OUT_SUB ";
	        DBResult dbrs = DBEngine.doSelect(con, sql, event.toConditions());
	        ResultSet rs = dbrs.getRs();
	        String allMoney = "0";
	        if(rs.next()){
	            allMoney = rs.getString(1) == null ? "0" : rs.getString(1);    
	        }
	        dbrs.clear();
	        allMoney = StringUtil.showPrice(allMoney);
	        //------------------------
	        
	        event.setStatus(status);
	        
	        for(int i=0;i<list.size();i++)
	        {
	            OutSub obj = (OutSub)list.get(i);
	            OutHead head = OutHead.fetchByOutBillId(con,obj.getOutBillId());
	            Product pro = Product.fetchByConcentricityCode(con,obj.getProductCode());
	            GSProductTypeDao typeDao = new GSProductTypeDao();
	            GSProductTypeBean bean = typeDao.findProductTypeByProuductCodeNoStauts(pro.getConcentricityCode().trim());
	            
	            WarehouseCategoryRelation warehouseCategoryRelation = WarehouseCategoryRelation.fetchByProductCode(con,obj.getProductCode());
	            WarehouseCategory warehouseCategory = null;
	            if(warehouseCategoryRelation!=null){
	            	warehouseCategory = WarehouseCategory.fetchbyCode(con,warehouseCategoryRelation.getCategoryCode().trim() , head.getWarehouseCode().trim());
	            }
	            if(pro==null) pro = new Product();
	            HSSFRow row = tSheet.createRow((short)i+1);
	            HSSFCell cell0  = createCell(tWb,row,(short)0, head.getWarehouseName().trim());
	            HSSFCell cell1  = createCell(tWb,row,(short)1, obj.viewPositionName(con));
	            HSSFCell cell2  = createCell(tWb,row,(short)2, obj.viewOutBillCode(con));
	            HSSFCell cell3  = createCell(tWb,row,(short)3, head.viewOutTime(con));
	            HSSFCell cell4  = createCell(tWb,row,(short)4, head.viewCustomerName(con));
	            HSSFCell cell5  = createCell(tWb,row,(short)5, head.viewOutType(con));
	            HSSFCell cell6  = createCell(tWb,row,(short)6, obj.viewGetBillCode(con));
	            HSSFCell cell7  = createCell(tWb,row,(short)7, obj.viewProviderName(con));
	            HSSFCell cell8  = createCell(tWb,row,(short)8, head.viewRepairBillCode(con));
	            HSSFCell cell9  = createCell(tWb,row,(short)9, obj.viewCustomerBusCode(con));
	            HSSFCell cell10  = createCell(tWb,row,(short)10, obj.viewProductCode(con));
	            HSSFCell cell11 = createCell(tWb,row,(short)11,pro.viewName(con));
	            HSSFCell cell12 = createCell(tWb,row,(short)12,pro.viewProductUnit(con));
	            HSSFCell cell13 = createCell(tWb,row,(short)13,pro.viewSpec(con));
	            HSSFCell cell14 = createCell(tWb,row,(short)14,pro.viewModel(con));
	            HSSFCell cell15 = createCell(tWb,row,(short)15,pro.viewProductBrand(con));
	            HSSFCell cell16 = createCell(tWb,row,(short)16,StringUtil.showCount(obj.viewProductCount(con)));
	            HSSFCell cell17 = createCell(tWb,row,(short)17,StringUtil.showPrice(obj.viewProductPrice(con)));
	            //郑斌    2008-03-21 10:16    begin
	            HSSFCell cell18 = createCell(tWb,row,(short)18,StringUtil.showPrice(obj.viewProductMoney(con)));
	            //郑斌    2008-03-21 10:18    end
	            HSSFCell cell19 = createCell(tWb,row,(short)19,obj.viewGetCustomerName(con));
	            HSSFCell cell20 = createCell(tWb,row,(short)20,obj.viewGetCustomerStaffName(con));
	            HSSFCell cell21 = createCell(tWb,row,(short)21,obj.viewBalanceMode(con));
	            WarehouseStaff staff = WarehouseStaff.fetch(con,event.getKeeperStaffId());
	            if(staff == null) staff = new WarehouseStaff();
	            HSSFCell cell22 = createCell(tWb,row,(short)22,staff.getName());
	            HSSFCell cell23 = createCell(tWb,row,(short)23,obj.getCreateUser());
	            HSSFCell cell24 = createCell(tWb,row,(short)24,obj.getCreateTime());
	            HSSFCell cell25 = createCell(tWb,row,(short)25,warehouseCategory==null?"":warehouseCategory.getCategoryName());
	            HSSFCell cell26 = createCell(tWb,row,(short)26,obj.getSubRemark());
	            if(bean==null){
	            	
	            }else{
	            	 HSSFCell cell27 = createCell(tWb,row,(short)27,DefineDic.getProductType(bean.getProductType()+""));
	            }
	        }
	        
	        HSSFRow row = tSheet.createRow((short)list.size()+1);
	        HSSFCell cell0  = createCell(tWb,row,(short)0, "");
	        HSSFCell cell1  = createCell(tWb,row,(short)1, "");
	        HSSFCell cell2  = createCell(tWb,row,(short)2, "");
	        HSSFCell cell3  = createCell(tWb,row,(short)3, "");
	        HSSFCell cell4  = createCell(tWb,row,(short)4, "");
	        HSSFCell cell5  = createCell(tWb,row,(short)5, "");
	        HSSFCell cell6  = createCell(tWb,row,(short)6, "");
	        
	        HSSFCell cell8  = createCell(tWb,row,(short)8, "");
	        HSSFCell cell9  = createCell(tWb,row,(short)9, "");
	        HSSFCell cell10  = createCell(tWb,row,(short)10, "");
	        HSSFCell cell11 = createCell(tWb,row,(short)11, "");
	        HSSFCell cell12 = createCell(tWb,row,(short)12, "");
	        HSSFCell cell13 = createCell(tWb,row,(short)13, "");
	        HSSFCell cell14 = createCell(tWb,row,(short)14, "");
	        HSSFCell cell15 = createCell(tWb,row,(short)15, "");
	        HSSFCell cell16 = createCell(tWb,row,(short)16, "");
	        //郑斌    2008-03-21 10:16    begin
	        HSSFCell cell17 = createCell(tWb,row,(short)17,"");
	        //郑斌    2008-03-21 10:18    end
	        HSSFCell cell18 = createCell(tWb,row,(short)18,"");
	        HSSFCell cell19 = createCell(tWb,row,(short)19,"");
	        HSSFCell cell20 = createCell(tWb,row,(short)20,"");
	        HSSFCell cell21 = createCell(tWb,row,(short)21,"");
	        HSSFCell cell22 = createCell(tWb,row,(short)22,"");
	        HSSFCell cell23 = createCell(tWb,row,(short)23,"");
	        HSSFCell cell24 = createCell(tWb,row,(short)24,"当前查询有效出库货物金额合计:");
	        HSSFCell cell25 = createCell(tWb,row,(short)25,allMoney);
	        HSSFCell cell26 = createCell(tWb,row,(short)26,"");
	        HSSFCell cell27 = createCell(tWb,row,(short)27,"");
	        
	        DateUtil date = new DateUtil();
	        String year = date.getYear() + "";
	        String month = date.getMonth() + "";
	        if (date.getMonth() < 10) month = "0" + date.getMonth();
	        String day = date.getDay() + "";
	        if (date.getDay() < 10) day = "0" + date.getDay();
	        String hour = date.getHour() + "";
	        if (date.getHour() < 10) hour = "0" + date.getHour();
	        String minute = date.getMinute() + "";
	        if (date.getMinute() < 10) minute = "0" + date.getMinute();
	        String second = date.getSecond() + "";
	        if (date.getSecond() < 10) second = "0" + date.getSecond();
	        
	        String fileName = "OUT_BILL_" + year + "_" + month + "_" + day + "_" + hour + "_" + minute + "_" + second + ".xls";
	        context.m_res.setContentType("application/ms-excel");
	        context.m_res.addHeader("Content-disposition", "filename="+fileName);
	        ServletOutputStream out = context.getServletOutputStream();
	        tWb.write(out);
	        out.flush();
	        out.close();
	    }
	
	

    //add by Wing end
	
	public void doAction(DBConnection con,SrvContext context,ILoginContext loginContext) throws Exception
	{
		String action_code = context.getActionCode();
		String path = "/system/base/";
		
		if(action_code.equalsIgnoreCase("listGetStatistic"))
		{
		    ObjectList warehouseList = Warehouse.fetchAllOrgs(con, loginContext.getLoginWarehouseCode());
            context.setSessionValue("WarehouseListDetail", warehouseList);
			listGetStatistic(con,context,loginContext,path);
			return;
		}
/*		if(action_code.equalsIgnoreCase("viewOutHead"))
		{
			String id = context.getString("Id",null);
			OutHead obj = OutHead.fetch(con,id);
			context.setValue("OutHead",obj);
			context.setNextUrl(path+"OutHeadView.jsp");
			return;
		}
		if(action_code.equalsIgnoreCase("deleteOutHead"))
		{
			context.setErrorUrl(path+"OutHeadList.jsp");
			deleteOutHead(con,context);
			backOutHead(con,context,path);
			return;
		}
			
		if(action_code.equalsIgnoreCase("toAddOutHead"))
		{
			OutHead obj = OutHead.fetch();
			OutHeadPageData data = (OutHeadPageData)PageData.fromTable(obj,OutHeadPageData.class);
			context.setValue("OutHeadPageData",data);
			context.setNextUrl(path+"OutHeadEdit.jsp");
			return;
		}
		if(action_code.equalsIgnoreCase("toUpdateOutHead"))
		{
			String id = context.getString("Id",null);
			OutHead obj = OutHead.fetch(con,id);
			OutHeadPageData data = (OutHeadPageData)PageData.fromTable(obj,OutHeadPageData.class);
			context.setValue("OutHeadPageData",data);
			context.setNextUrl(path+"OutHeadEdit.jsp");
			return;
		}
		if(action_code.equalsIgnoreCase("toCopyOutHead"))
		{
			String id = context.getString("Id",null);
			OutHead obj = OutHead.copy(OutHead.fetch(con,id));
			OutHeadPageData data = (OutHeadPageData)PageData.fromTable(obj,OutHeadPageData.class);
			context.setValue("OutHeadPageData",data);
			context.setNextUrl(path+"OutHeadEdit.jsp");
			return;
		}
		if(action_code.equalsIgnoreCase("saveOutHead"))
		{
			context.setErrorUrl(path+"OutHeadEdit.jsp");
			saveOutHead(con,context,loginContext);
			backOutHead(con,context,path);
			return;
		}
		if(action_code.equalsIgnoreCase("saveAndNewOutHead"))
		{
			context.setErrorUrl(path+"OutHeadEdit.jsp");
			saveOutHead(con,context,loginContext);
			OutHead obj = OutHead.fetch();
			OutHeadPageData data = (OutHeadPageData)PageData.fromTable(obj,OutHeadPageData.class);
			context.setValue("OutHeadPageData",data);
			context.setNextUrl(path+"OutHeadEdit.jsp");			
			return;
		}
		if(action_code.equalsIgnoreCase("backListOutHead"))
		{
			backOutHead(con,context,path);
			return;
		}
*/		
		//add by Wing start
		if(action_code.equalsIgnoreCase("exportGetStatistic"))
        {
		    exportGetStatistic(con,context,path);
            return;
        }

		if(action_code.equalsIgnoreCase("exportDetailStatistic"))
        {
			String customerCode = context.getString("s_GetCustomerCode");
		    exportDetailStatistic(con,context,path,customerCode);
            return;
        }
		//add by Wing end

		Logger.throwException("GetStatisticSrv","unknown action_code="+action_code);
	}
}
